package edu.zk.web.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;

import org.apache.log4j.Logger;

import com.zk.db.utils.DBConnection;
import com.zk.db.utils.DBUtils;
import com.zk.db.utils.JdbcTemplate;

import edu.zk.web.dao.iface.UserDao;
import edu.zk.web.domain.Page;
import edu.zk.web.domain.UMQueryKeyword;
import edu.zk.web.domain.User;
import edu.zk.web.domain.UserDetail;

public class UserJdbcDaoImpl implements UserDao {

	private static Logger log=Logger.getLogger(UserJdbcDaoImpl.class);
	
	@Override
	public int checkUser(String name, String password) {
		var rowNum=0;
		String sql="select count(1) from users where name=? and password=?";
		rowNum=JdbcTemplate.queryForCount(sql, name,password);
		return rowNum;
	}

	Integer i;
	
	@Override
	public int insertUser(User user) {
		var userId=-1;
		String sql="insert into users values(users_id.nextval,?,?,?,?,?)";
		Connection connection=DBConnection.getConnection();
		try {
			PreparedStatement psmt=connection.prepareStatement(sql,new String[] {"id"});
			psmt.setString(1, user.getName());
			psmt.setString(2, user.getNickName());
			psmt.setString(3, user.getPassword());
			psmt.setString(4, user.getSex());
			psmt.setString(5, user.getEmail());
			DBUtils.printRealSql(sql, new Object[] {user.getName(),user.getNickName(),user.getPassword(),user.getSex(),user.getEmail()});
			int rowNum=psmt.executeUpdate();
			ResultSet rs=psmt.getGeneratedKeys();
			if(rowNum==1&&rs.next()) {
				userId=rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return userId;
	}

	@Override
	public int insertUserDetail(UserDetail ud) {
		var rowNum=0;
		String sql="insert into userDetail values(userdetail_id.nextval,?,?,?)";
		rowNum=JdbcTemplate.update(sql, ud.getNativePlace_code(),ud.getHobby_code(),ud.getUserId());
		return rowNum;
	}

	@Override
	public List<Map<String, Object>> queryAll() {
		String sql="select " + 
				"u.id,u.name,u.nickname,u.password,u.sex,u.email,np.code nativePlace_code,np.name nativePlace_name,ud.hobby_code " + 
				"from users u left outer join Userdetail ud on u.id=ud.userid " + 
				"left outer join nativeplace np on ud.nativeplace_code=np.code order by u.id";
		List<Map<String,Object>> list=JdbcTemplate.query(sql, (rs)->{
			List<Map<String,Object>> list0=new ArrayList<>();
			try {
				while(rs.next()) {
					Map<String,Object> map=new HashMap<>();
					for(int i=1,len=rs.getMetaData().getColumnCount();i<=len;i++){
						map.put(rs.getMetaData().getColumnName(i), rs.getObject(i));
					}
					list0.add(map);
				}
			} catch (SQLException e) {
				log.debug("queryall method error,message is "+e.getMessage());
			}
			return list0;
		}, null);
		return list;
	}

	@Override
	public int deleteUsers(String ids) {
		var rowNum=0;
		String sql="delete from users where id in ("+ids+")";
		rowNum=JdbcTemplate.update(sql,new Object[] {});
		return rowNum;
	}

	@Override
	public int deleteUserDetail(String ids) {
		var rowNum=0;
		String sql="delete from userDetail where userid in ("+ids+")";
		rowNum=JdbcTemplate.update(sql,new Object[] {});
		return rowNum;
	}

	@Override
	public Map<String, Object> queryUserById(String id) {
		String sql="select " + 
				"u.id,u.name,u.nickname,u.password,u.sex,u.email,np.code nativePlace_code,np.name nativePlace_name,ud.hobby_code " + 
				"from users u left outer join Userdetail ud on u.id=ud.userid " + 
				"left outer join nativeplace np on ud.nativeplace_code=np.code where u.id=? order by u.id";
		Map<String,Object> map=JdbcTemplate.query(sql, (ResultSet rs)->{
			Map<String,Object> map0=new HashMap<>();
			try {
				if(rs.next()) {
					for(int i=1,len=rs.getMetaData().getColumnCount();i<=len;i++){
						map0.put(rs.getMetaData().getColumnName(i), rs.getObject(i));
					}
				}
			} catch (SQLException e) {
				log.debug("queryUserById method error,message is "+e.getMessage());
			}
			return map0;
		}, new Object[] {id});
		return map;
	}

	@Override
	public int updateUser(User user) {
		var rowNum=0;
		String sql="update users set name=?,nickname=?,password=?,sex=?,email=? where id=?";
		rowNum=JdbcTemplate.update(sql, user.getName(),user.getNickName(),user.getPassword(),user.getSex(),user.getEmail(),user.getId());
		return rowNum;
	}

	@Override
	public int updateUserDetail(UserDetail userDetail) {
		var rowNum=0;
		String sql="update userDetail set nativeplace_code=?,hobby_code=? where userId=?";
		rowNum=JdbcTemplate.update(sql, userDetail.getNativePlace_code(),userDetail.getHobby_code(),userDetail.getUserId());
		return rowNum;
	}

	@Override
	public void queryByPage(Page<List<Map<String, Object>>> page) {
		String sql="select * from (" + 
				"  select baseTable.*,rownum as rn from (" + 
				"    select u.id,u.name,u.nickname,u.password,u.sex,u.email,np.name nativePlace_name,ud.hobby_code " + 
				"           from users u left outer join Userdetail ud on u.id=ud.userid " + 
				"           left outer join nativeplace np on ud.nativeplace_code=np.code order by u.id) baseTable " + 
				"  where rownum<=(?)*?) " + 
				"where rn>(?-1)*?";
		List<Map<String,Object>> list=JdbcTemplate.query(sql, (rs)->{
			List<Map<String,Object>> list0=new ArrayList<>();
			try {
				while(rs.next()) {
					Map<String,Object> map=new HashMap<>();
					for(int i=1,len=rs.getMetaData().getColumnCount();i<=len;i++){
						map.put(rs.getMetaData().getColumnName(i), rs.getObject(i));
					}
					list0.add(map);
				}
			} catch (SQLException e) {
				log.debug("query by page method error,message is "+e.getMessage());
			}
			return list0;
		}, new Object[] {page.getCurrentPage(),page.getRowNumber(),page.getCurrentPage(),page.getRowNumber(),});
		page.setPageData(list);
	}

	@Override
	public void getTotalRow(Page<List<Map<String, Object>>> page) {
		String sql="select count(1) from users u left outer join Userdetail ud on u.id=ud.userid" + 
				" left outer join nativeplace np on ud.nativeplace_code=np.code order by u.id";
		int totalRow=JdbcTemplate.queryForCount(sql, new Object[] {});
		
		page.setTotalRow(totalRow);
	}

	@Override
	public void queryByPage(Page<List<Map<String, Object>>> page, UMQueryKeyword qk) {
		List<Object> params=new ArrayList<>();
		if(!Objects.equals("",qk.getName())) {
			params.add("%"+qk.getName()+"%");
		}
		if(!Objects.equals("",qk.getNickName())) {
			params.add("%"+qk.getNickName()+"%");
		}
		if(!Objects.equals("",qk.getSex())) {
			params.add(qk.getSex());
		}	
		if(!Objects.equals("",qk.getHobbyCode())) {
			params.add("%"+qk.getHobbyCode()+"%");
		}
		params.add(page.getCurrentPage());
		params.add(page.getRowNumber());
		params.add(page.getCurrentPage());
		params.add(page.getRowNumber());
		String beginSQL="select * from ( select baseTable.*,rownum as rn from (";
		String SQL="select u.id,u.name,u.nickname,u.password,u.sex,u.email,np.name nativePlace_name,ud.hobby_code " + 
				"from users u left outer join Userdetail ud on u.id=ud.userid " + 
				"left outer join nativeplace np on ud.nativeplace_code=np.code where 1=1 ";
		if(!Objects.equals("",qk.getName())) {
			SQL+="and u.name like ? ";
		}
		if(!Objects.equals("",qk.getNickName())) {
			SQL+="and u.nickname like ? ";
		}
		if(!Objects.equals("",qk.getSex())) {
			SQL+="and u.sex=? ";
		}
		if(!Objects.equals("",qk.getHobbyCode())) {
			SQL+="and ud.hobby_code like ?";		
		}	
		String endSQL=" order by u.id) baseTable where rownum<=(?)*?) where rn>(?-1)*?";
		List<Map<String,Object>> list=JdbcTemplate.query(beginSQL+SQL+endSQL, (rs)->{
			List<Map<String,Object>> list0=new ArrayList<>();
			try {
				while(rs.next()) {
					Map<String,Object> map=new HashMap<>();
					for(int i=1,len=rs.getMetaData().getColumnCount();i<=len;i++){
						map.put(rs.getMetaData().getColumnName(i), rs.getObject(i));
					}
					list0.add(map);
				}
			} catch (SQLException e) {
				log.debug("query by page method error,message is "+e.getMessage());
			}
			return list0;
		}, params.toArray());
		page.setPageData(list);
		
	}

	@Override
	public void getTotalRow(Page<List<Map<String, Object>>> page, UMQueryKeyword qk) {
		List<Object> params=new ArrayList<>();
		if(!Objects.equals("",qk.getName())) {
			params.add("%"+qk.getName()+"%");
		}
		if(!Objects.equals("",qk.getSex())) {
			params.add(qk.getSex());
		}	
		if(!Objects.equals("",qk.getHobbyCode())) {
			params.add("%"+qk.getHobbyCode()+"%");
		}
		String sql="select count(1) from users u left outer join Userdetail ud on u.id=ud.userid" + 
				" left outer join nativeplace np on ud.nativeplace_code=np.code where 1=1 ";
		if(!Objects.equals("",qk.getName())) {
			sql+="and u.name like ? ";
		}
		if(!Objects.equals("",qk.getSex())) {
			sql+="and u.sex=? ";
		}
		if(!Objects.equals("",qk.getHobbyCode())) {
			sql+="and ud.hobby_code like ?";
		}
		sql+=" order by u.id";
		int totalRow=JdbcTemplate.queryForCount(sql, params.toArray());
		page.setTotalRow(totalRow);
		
	}

}


